﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Logistics.DataManage.DAL;
using MapgisEbp.QuickDataBase;
using Logistics.DataManage.Local;
using System.Data;

namespace Logistics.DataManage
{
    class CustomerDAO
    {
        public static OCustomer GetCustomerById(String customerId)
        {
            String sql = "select * from Customs where CustomID=?";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, customerId);
            DataSet ds = LocalDb.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            IList<OCustomer> list = ConvertTo(ds);
            return list.Count == 0 ? null : list[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="phoneNumber"></param>
        /// <param name="outletId"></param>
        /// <returns></returns>
        public static IList<OCustomer> GetCustomerByPhoneNumber(String phoneNumber, String outletId)
        {
            ParameterizedSQL PSQL = null;
            if (String.IsNullOrWhiteSpace(outletId))
            {
                String sql = "select * from Customs where CustomTel=? or CustomMobile=? ";
                PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, phoneNumber, phoneNumber);
            }
            else
            {
                String sql = "select * from Customs where (CustomTel=? or CustomMobile=?) and NetworkName=? ";
                PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, phoneNumber, phoneNumber, outletId);
            }
            DataSet ds = LocalDb.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            IList<OCustomer> list = ConvertTo(ds);
            return list.Count == 0 ? null : list;
        }

        public static DataSet GetCustomers(String outletId, String customerName, String mobile, String tel, String BankNum)
        {
            IList<Object> paras = new List<Object>();
            String sql = "select * from Customs where 1=1";
            if (!String.IsNullOrEmpty(outletId))
            {
                sql += " and NetworkName=?";
                paras.Add(outletId);          //NetworkName存Id，鄙视
            }
            if (!string.IsNullOrEmpty(customerName))
            {
                sql += " and CustomName like ?";
                paras.Add("%" + customerName + "%");
            }
            if (!string.IsNullOrEmpty(mobile))
            {
                sql += " and CustomMobile like ?";
                paras.Add("%" + mobile + "%");
            }
            if (!string.IsNullOrEmpty(tel))
            {
                sql += " and CustomTel like ?";
                paras.Add("%" + tel + "%");
            }
            if (!string.IsNullOrEmpty(BankNum))
            {
                sql += " and BankID =?";
                paras.Add(BankNum);
            }
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, paras.ToArray());
            DataSet ds = LocalDb.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            return ds;
        }

        //public static DataSet GetCustomersBySiteId(String siteId)
        //{
        //    String sql = "select * from Customs where SiteName=?";

        //    //String sql = "select * from Customs where SiteName in(select networkid from networkinfo where siteid = ?)";
        //    ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, siteId);
        //    DataSet ds = LocalDb.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
        //    return ds;
        //}

        public static DataSet GetCustomersByOutletId(String outletId)
        {
            String sql = "select * from Customs where NetworkName = ? ";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, outletId);
            DataSet ds = LocalDb.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            return ds;
        }

        public static DataSet GetCustomersByOutletId(String outletId, string number)
        {
            String sql = "select * from Customs where NetworkName = ? and (customtel=? or custommobile=?)";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, outletId, number, number);
            DataSet ds = LocalDb.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            return ds;
        }


        private static IList<OCustomer> ConvertTo(DataSet ds)
        {
            IList<OCustomer> list = new List<OCustomer>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    OCustomer customer = new OCustomer();
                    customer.CustomID = Convert.ToString(row["CustomID"]);
                    customer.CompanyAddress = Convert.ToString(row["Address"]);
                    customer.CustomName = Convert.ToString(row["CustomName"]);
                    customer.BankName = Convert.ToString(row["BankName"]);
                    customer.BankNum = Convert.ToString(row["BankID"]);
                    customer.CustomTel = Convert.ToString(row["CustomTel"]);
                    customer.CustomMobile = Convert.ToString(row["CustomMobile"]);
                    list.Add(customer);
                }
            }
            return list;
        }

        #region 运单录入客户信息处理事件
        public static void JudeConsignee(OWaybill model)
        {
            string Consignee = model.Consigneeor;
            string ConsigneeTel = model.ConsigneeTel;
            string ConsigneeMobile = model.ConsigneeMobile;
            string ConsigneeAddr = model.ConsigneeAddress;
            string siteId = model.Destinations;
            string outletId = model.DestinationsNetwork;//客户信息中存储网点编号
            SaveCustomer(Consignee, ConsigneeTel, ConsigneeMobile, ConsigneeAddr, "", siteId, outletId,"");
        }

        public static void JudeConsignor(OWaybill model)
        {
            string Consignor = model.Consignor;
            string ConsignorTel = model.ConsignorTel;
            string ConsignorMobile = model.ConsignorMobile;
            string ConsignorAddr = model.ConsignorAddress;
            string BankNum = model.ConsignorCardID;
            string BankName = model.ConsignorBank;
            string siteId = model.Origin;
            string outletId = model.OriginNetWork;//客户信息中存储网点编号
            SaveCustomer(Consignor, ConsignorTel, ConsignorMobile, ConsignorAddr, BankNum, siteId, outletId, BankName);
        }
        public static void SaveCustomer(String name, String tel, String cellphone, String addr, String BankID, String siteId, String outletId, string BankName)
        {
            if (String.IsNullOrEmpty(name)) return;
            string sql = "select Count(*) from Customs where CustomTel=? and CustomMobile=? and CustomName=? ";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, tel, cellphone, name);
            Object obj = LocalDb.Handler.ExecuteScalar(PSQL.SQL, PSQL.Parameters);
            if (Convert.ToInt32(obj) == 0)
            {
                String insertSQL = "INSERT INTO Customs(CustomID,CustomName,CustomTel,CustomMobile,Address,BankID,SiteName,NetworkName,CreateDate,IsChecked,bankname)values";
                insertSQL += "(?,?,?,?,?,?,?,?,?,?,?)";
                PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(insertSQL,
                    GetNewCustomerId(),
                    name,
                    tel,
                    cellphone,
                    addr,
                    BankID,
                    siteId,
                    outletId,
                    DateTime.Now,
                    false, BankName);
                LocalDb.Handler.ExecuteNonQuery(PSQL.SQL, PSQL.Parameters);
            }
        }
        /// <summary>
        /// 获取编号（年+月+日+maxID）
        /// </summary>
        /// <returns></returns>
        private static string GetNewCustomerId()
        {
            String sql = "select Count(CustomID) from Customs";
            Object obj = LocalDb.Handler.ExecuteScalar(sql);
            int num = Convert.ToInt32(obj);
            String Date = DateTime.Now.ToString("yyMMdd");
            return Date + (num + 1);
        }
        #endregion
    }
}
